package job;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class StudentDao {

    public int add(Student stu) {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        try {
            String sql = "INSERT INTO s_student(sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,pass) VALUES(?,?,?,?,?,?,?,?,?,?);";
            ps = connection.prepareStatement(sql);
            System.out.println(sql);
            ps.setString(1, stu.getSname());
            ps.setString(2, stu.getSex());
            ps.setString(3, stu.getTelephone());
            ps.setString(4, stu.getQq());
            ps.setString(5, stu.getWechat());
            ps.setLong(6, stu.getGradeid());
            ps.setObject(7, stu.getEnterdate());
            ps.setString(8, stu.getFromcity());
            ps.setObject(9, stu.getBirthday());
            ps.setString(10, stu.getPass());
            int num = ps.executeUpdate();
            return 1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
        }
        return 0;
    }

    public int update(Student stu) {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        try {
            String sql = "update s_student set sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,enterdate=?,fromcity=?,birthday=?,pass=? where stuno=?";
            ps = connection.prepareStatement(sql);
            ps.setString(1, stu.getSname());
            ps.setString(2, stu.getSex());
            ps.setString(3, stu.getTelephone());
            ps.setString(4, stu.getQq());
            ps.setString(5, stu.getWechat());
            ps.setLong(6, stu.getGradeid());
            ps.setObject(7, stu.getEnterdate());
            ps.setString(8, stu.getFromcity());
            ps.setObject(9, stu.getBirthday());
            ps.setString(10, stu.getPass());
            ps.setInt(11, stu.getStuno());
            int num = ps.executeUpdate();
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
        }
        return 0;
    }

    public int del(long stuno) {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        try {
            String sql = "delete from s_student  where stuno=?";
            ps = connection.prepareStatement(sql);
            ps.setLong(1, stuno);
            int num = ps.executeUpdate();
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
        }
        return 0;
    }

    public Student query(int stuno) {
        //查询
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("select * from s_student where stuno=? order by stuno desc");
            ps.setInt(1, stuno);
            rs = ps.executeQuery();
            Student su = new Student();
            while (rs.next()) {
                su.setStuno(rs.getInt("stuno"));
                su.setSname(rs.getString("sname"));
                su.setSex(rs.getString("sex"));
                su.setTelephone(rs.getString("telephone"));
                su.setQq(rs.getString("qq"));
                su.setWechat(rs.getString("wechat"));
                su.setGradeid(rs.getInt("gradeid"));
                su.setEnterdate(rs.getDate("enterdate"));
                su.setFromcity(rs.getString("fromcity"));
                su.setBirthday(rs.getDate("birthday"));
                su.setPass(rs.getString("pass"));
            }
            return su;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, connection);
        }
        return null;

    }

    public List<Student> query(String sname, int y, int t) {
        //按照名字查询
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList();
        try {
            ps = connection.prepareStatement("select * from s_student  where sname like \"%\"?\"%\"  order by stuno limit ?,?");
            ps.setString(1, sname);
            ps.setInt(2, (y - 1) * t);
            ps.setInt(3, t);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student su = new Student();
                su.setStuno(rs.getInt("stuno"));
                su.setSname(rs.getString("sname"));
                su.setSex(rs.getString("sex"));
                su.setTelephone(rs.getString("telephone"));
                su.setQq(rs.getString("qq"));
                su.setWechat(rs.getString("wechat"));
                su.setGradeid(rs.getInt("gradeid"));
                su.setEnterdate(rs.getDate("enterdate"));
                su.setFromcity(rs.getString("fromcity"));
                su.setBirthday(rs.getDate("birthday"));
                su.setPass(rs.getString("pass"));
                list.add(su);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, connection);
        }
        return null;

    }

    public List<Student> query(String sname, String qq, String telephone, int y, int t) {
        //按照名字查询
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList();
        try {
            ps = connection.prepareStatement("select * from s_student  where sname like \"%\"?\"%\" and  qq like \"%\"?\"%\"  and  telephone like   \"%\"?\"%\" order by stuno limit ?,?");
            ps.setString(1, sname);
            ps.setString(2, qq);
            ps.setString(3, telephone);
            ps.setInt(4, (y - 1) * t);
            ps.setInt(5, t);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student su = new Student();
                su.setStuno(rs.getInt("stuno"));
                su.setSname(rs.getString("sname"));
                su.setSex(rs.getString("sex"));
                su.setTelephone(rs.getString("telephone"));
                su.setQq(rs.getString("qq"));
                su.setWechat(rs.getString("wechat"));
                su.setGradeid(rs.getInt("gradeid"));
                su.setEnterdate(rs.getDate("enterdate"));
                su.setFromcity(rs.getString("fromcity"));
                su.setBirthday(rs.getDate("birthday"));
                su.setPass(rs.getString("pass"));
                list.add(su);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, connection);
        }
        return null;

    }
}
